﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

using Microsoft.ApplicationBlocks.Data;

using YouHP.IDAL;
using YouHP.Model;

namespace YouHP.SqlServerDAL
{
    public class DoctorDAL : IDoctor
    {
        static readonly string _GetDoctorListEx = "GetDoctorListEx";

        #region IDoctor Members

        public DoctorInfo GetDoctorInfo(int did)
        {
            IDataReader reader = SqlHelper.ExecuteReader(
                Common.ConnStr,
                Common.DoctorSP_GetDoctorById,
                did)
                as IDataReader;
            return CBO.FillObject<DoctorInfo>(reader);
        }

        public List<DoctorInfo> GetDoctorInfoByNameHospital(string name, string hospital)
        {
            string condStr = string.Empty;
            if ((name != null && name != "") && (hospital == null || hospital == ""))
            {
                condStr = string.Format(" [Doctor].[Name] like '%{0}%'", name);
            }
            else if ((name == null || name == "") && (hospital != null || hospital != ""))
            {
                condStr = string.Format(" [Doctor].[Hospital] like '%{0}%'", hospital);
            }
            else
            {
                condStr = string.Format(" [Doctor].[Name] like '%{0}%' AND [Doctor].[Hospital] like '%{1}'", name, hospital);
            }
            IDataReader reader = SqlHelper.ExecuteReader(
                Common.ConnStr,
                Common.DoctorSP_GetDoctor,
                condStr)
                as IDataReader;
            return CBO.FillCollection<DoctorInfo>(reader);
        }

        public int AddDoctor(DoctorInfo info)
        {
            SqlConnection conn = new SqlConnection(Common.ConnStr);
            conn.Open();
            SqlTransaction ts = conn.BeginTransaction();
            SqlParameter[] outParms = new SqlParameter[]  {
                new SqlParameter("@Name", SqlDbType.NVarChar, 50),
                new SqlParameter("@Hospital", SqlDbType.NVarChar, 50),
                new SqlParameter("@IsFamous", SqlDbType.Int),
                new SqlParameter("@Description", SqlDbType.NVarChar, Int32.MaxValue),
                new SqlParameter("@PrescriptionCount", SqlDbType.Int),
                new SqlParameter("@PrescriptionAverageScore", SqlDbType.Float),
                new SqlParameter("@DiseaseId", SqlDbType.Int),
                new SqlParameter("@CreateTime", SqlDbType.DateTime),
                new SqlParameter("@Id", SqlDbType.Int)
            };
            
            outParms[0].Value = info.Name;
            outParms[1].Value = info.Hospital;
            outParms[2].Value = info.IsFamous;
            outParms[3].Value = info.Description;
            outParms[4].Value = info.PrescriptionCount;
            outParms[5].Value = info.PrescriptionAverageScore;
            outParms[6].Value = info.DiseaseId;
            outParms[7].Value = info.CreateTime;
            outParms[8].Direction = ParameterDirection.Output;

            SqlHelper.ExecuteNonQuery(ts, CommandType.StoredProcedure, Common.DoctorSP_AddDoctor, outParms);
            ts.Commit();
            conn.Close();
            return Convert.ToInt32(outParms[8].Value.ToString());
        }

        public bool UpdateDoctor(DoctorInfo info)
        {
            int count = SqlHelper.ExecuteNonQuery(
                 Common.ConnStr,
                 Common.DoctorSP_UpdateDoctor,
                 info.Id,
                 info.Name,
                 info.Hospital,
                 info.IsFamous,
                 info.Description,
                 info.PrescriptionCount,
                 info.PrescriptionAverageScore,
                 info.DiseaseId,
                 info.CreateTime);
            return count > 0;
        }

        public bool DeleteDoctor(int did)
        {
            int count = SqlHelper.ExecuteNonQuery(
                Common.ConnStr,
                Common.DoctorSP_DeleteDoctorById,
                did);
            return count > 0;
        }

        public List<DoctorInfo> GetDoctorList(string condStr)
        {
            IDataReader dr = SqlHelper.ExecuteReader(Common.ConnStr, Common.DoctorSP_GetDoctor, condStr);

            return CBO.FillCollection<DoctorInfo>(dr);
        }

        public List<DoctorInfo> GetDoctorList(int pageSize, int pageIndex, string condStr, out int count)
        {
            string orderStr = "[Id] asc";
            return GetDoctorList(pageSize, pageIndex, condStr, orderStr, out count);
        }

        public List<DoctorInfo> GetDoctorList(int pageSize, int pageIndex, string condStr, string orderStr, out int count)
        {
            DataSet ds = SqlHelper.ExecuteDataset(
                Common.ConnStr,
                Common.DoctorSP_GetDoctorList,
                pageSize,
                pageIndex,
                condStr,
                orderStr);

            IDataReader dr = ds.Tables[0].CreateDataReader();
            if (dr != null && dr.Read())
            {
                count = dr.GetInt32(0);
                dr.Close();
            }
            else
            {
                count = 0;
            }

            dr = ds.Tables[1].CreateDataReader();
            return CBO.FillCollection<DoctorInfo>(dr);
        }

        // Use refined stored procedure.
        public List<DoctorInfo> GetDoctorListEx(int pageSize, int pageIndex, int diseaseId, out int count)
        {
            DataSet ds = SqlHelper.ExecuteDataset(
                Common.ConnStr,
                _GetDoctorListEx,
                pageSize,
                pageIndex,
                diseaseId);

            IDataReader dr = ds.Tables[0].CreateDataReader();
            if (dr != null && dr.Read())
            {
                count = dr.GetInt32(0);
                dr.Close();
            }
            else
            {
                count = 0;
            }

            dr = ds.Tables[1].CreateDataReader();
            return CBO.FillCollection<DoctorInfo>(dr);
        }

        #endregion
    }
}
